package com.kkm.dao.Imp;

import com.kkm.dao.SongListDao;
import com.kkm.pojo.Song;
import com.kkm.pojo.SongList;
import com.kkm.util.JdbcUtils_Druid;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class SongListDaoImp implements SongListDao {
    private JdbcTemplate template = new JdbcTemplate(JdbcUtils_Druid.getDataSource());

    @Override
    public List<SongList> showAll(int index, int rows) {
        //先查出来，显不显示在页面上再说
        String sql = "SELECT id,title,pic,introduction,style FROM `song_list` limit ?,?";
        return template.query(sql, new BeanPropertyRowMapper<>(SongList.class), index, rows);
    }

    @Override
    public int checkSongListCount() {
        String sql = "select count(*)  from `song_list` ";
        return template.queryForObject(sql, int.class);
    }

    @Override
    public int checkSongListNum(Map<String, String[]> condition) {
        try {

            String sql = "select count(*) from `song_list`  where 1=1";
            //解析map  1.对sql拼接
            StringBuffer sb = new StringBuffer(sql);
            //2.获取键值，name address，email
            Set<String> keySet = condition.keySet();
            //3.定义参数集合
            List<Object> params = new ArrayList<Object>();
            //遍历看那个不为空拼接到sql语句中
            //key：name，address，email
            for (String key : keySet) {
                //排除分页条件参数
                if ("currentPage".equals(key) || "rows".equals(key) || "method".equals(key)) {
                    continue;
                }
                String value = condition.get(key)[0];//获取值
                if (value != null || "".equals(value)) {
                    //有值
                    sb.append(" and " + key + " like ? ");
                    params.add("%%" + value + "%");
                }
            }
            System.out.println("1111" + sb.toString());
            return template.queryForObject(sb.toString(), int.class, params.toArray());
        } catch (Exception e) {
            return 0;
        }
    }

    @Override
    public List<SongList> checkSongList(Map<String, String[]> condition, int index, int rows) {
        try {
            String sql = "select * from song_list where 1=1 ";
            //解析map  1.对sql拼接
            StringBuffer sb = new StringBuffer(sql);
            //2.获取键值，name address，email
            Set<String> keySet = condition.keySet();
            //3.定义参数集合
            List<Object> params = new ArrayList<Object>();
            //遍历看那个不为空拼接到sql语句中
            //key：name，address，email
            for (String key : keySet) {
                //排除分页条件参数
                if ("currentPage".equals(key) || "rows".equals(key) || "method".equals(key)) {
                    continue;
                }
                String value = condition.get(key)[0];//获取值
                if (value != null || "".equals(value)) {
                    //有值
                    sb.append(" and " + key + " like ? ");
                    params.add("%%" + value + "%");
                }
            }
            sb.append(" limit ?,? ");
            params.add(index);
            params.add(rows);
            sql = sb.toString();
            return template.query(sql, new BeanPropertyRowMapper<SongList>(SongList.class), params.toArray());
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public void deleteSongList(int sid) {
        String sql = "delete from song_list where id=? ";
        template.update(sql, sid);
    }

    @Override
    public SongList SongListById(int id) {
        String sql = "select * from song_list where id=?";
        return template.queryForObject(sql, new BeanPropertyRowMapper<SongList>(SongList.class), id);
    }

    @Override
    public void modifySongList(String address, int sid) {
//        String sql="update user set username=?,sex=?,phone_num=?,email=?,birth=?,location=? where id=?";
//        template.update(sql,username,sex,phone,email,birth,address,uid);
    }

    @Override
    public void addSongList(String title, String pic, String introduction, String style) {
        String sql = "insert into song_list (title,pic,introduction,style) values(?,?,?,?)";
        template.update(sql, title, pic, introduction, style);
    }

    @Override
    public int checkMusicListCount(String slid) {
        String sql = "select count(*) from list_song where song_list_id=?";
        return template.queryForObject(sql, int.class, slid);
    }

    @Override
    public List<Song> showMusicListSong(String slid, int index, int rows) {
        String sql = "select song.* from list_song,song where list_song.song_id=song.id and list_song.song_list_id= ? limit ?,?";
        return template.query(sql, new BeanPropertyRowMapper<Song>(Song.class),slid, index, rows);
    }
}
